package util;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;

public class Update_Status {
	private Connection conn = null;
	private PreparedStatement pt = null;
	private Statement st = null;
	private ResultSet rs = null;
	private boolean flag = false;
	private String sql = "";
	/**
	 * 
	 *@author jhc
	 *@function:根据日期得到MAS表中天表的名称，以此来修改表中相应数据的状态
	 * @param date
	 *Aug 6, 2010
	 *9:23:05 AM
	 *bzsafety
	 *util
	 *void
	 */
	public void updateSmsStatus(String dateTime,String date){
		
		Connection con = null;
		PreparedStatement pst =null;
		PreparedStatement pstT =null;
		ResultSet rs = null;
		ResultSet rsT = null;
		
		try
		{
		ArrayList<String> smsList = getSms(dateTime);//得到自己表中要修改状态的记录
			if(smsList!=null&&smsList.size()>0)
			{
				ArrayList modifyStatusList = getSmsStatus(smsList,date);
				if(modifyStatusList!=null&&modifyStatusList.size()>0)
				{
					updateStatus(modifyStatusList);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
/**
 * jhc
 * @param dateTime
 * @return
 */
	public ArrayList getSms(String dateTime){
		String lastDateTime = dateTime.substring(0, 10);
		//System.out.println("lastDateTime==="+lastDateTime);
		ArrayList smsIdList = new ArrayList();
		try
		{
			conn =  ConnectDB.getConn("zzClub");
			String sql = "select * from tb_sms_send where sendTime like '%"+lastDateTime+"%' and smsSendState=0";
			//System.out.println("得到要"+sql);
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			while(rs.next())
			{
				String smsId = String.valueOf(rs.getInt("smsId"));
				
					smsIdList.add(smsId);
				
				
			}
		}catch(Exception e){
			e.printStackTrace();
		} finally {
			ConnectDB.closeSqlConnection();
		}
		return smsIdList;
	}
	/**
	 * 
	 *@author jhc
	 *@function:根据从自己表中得到的要修改的短信ID从MAS表中得到此短信的发送状态
	 * @param smsIdList
	 * @return
	 *Oct 20, 2010
	 *10:35:49 AM
	 *bzsafety
	 *util
	 *ArrayList
	 */
	public ArrayList getSmsStatus(ArrayList smsIdList,String date){
		String tableName = "tbl_SMResult_"+date;//得到MAS中天表的名称
		String smsId ="";
		ArrayList smsResultList = new ArrayList();
		try
		{
			conn =ConnectDB.getConn("DB_CustomSMS");
			for(int i = 0;i<smsIdList.size();i++)
			{
				 smsId = smsIdList.get(i).toString()+",";
			}
			String lastSmsId = smsId.substring(0,smsId.length()-1);
			String hql = "select Recv_Status,Reserve2 from "+tableName +" where Reserve2 in('"+lastSmsId+"')";
			//System.out.println("得到MAS表中"+hql);
			pt = conn.prepareStatement(hql);
			rs = pt.executeQuery();
			while(rs.next())
			{
				String status = rs.getString("Recv_Status");
				String sms_Id = rs.getString("Reserve2");
				Hashtable ht = new Hashtable();
				ht.put(sms_Id, status);
				smsResultList.add(ht);
			}
		}catch(Exception e){
			e.printStackTrace();
		} finally {
			ConnectDB.closeSqlConnection();
		}
		return smsResultList;
	}
	/**
	 * 
	 *@author jhc
	 *@function:根据从MAS表中得到要修改状态的记录更新自己表中的记录
	 * @param list
	 *Oct 20, 2010
	 *10:47:08 AM
	 *bzsafety
	 *util
	 *void
	 */
	public void updateStatus(ArrayList list){
		try
		{
			conn =ConnectDB.getConn("zzClub");
			st = conn.createStatement();
			for(int i = 0;i<list.size();i++)
			{
				Hashtable ht = (Hashtable)list.get(i);
				Iterator it  = ht.keySet().iterator();
				while(it.hasNext())
				{
					String smsId = it.next().toString();//得到表sms_send中的主键
					String status = ht.get(smsId).toString();//根据smsId得到此记录的状态
					int zhuangTai = 0;
					if(Integer.parseInt(status)==0){
						zhuangTai=1;
					}else {
						zhuangTai=2;
					}
					String hql = "update tb_sms_send set smsSendState ="+zhuangTai+" where smsId="+Integer.parseInt(smsId);
					//System.out.println("修改自己的表"+hql);
					st.addBatch(hql);
				}
			}
			st.executeBatch();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public static void main(String[] args) throws Exception {
		Connection conn =ConnectDB.getSqlServerConnectionMas();
		System.out.println(conn);
	}
}
